package com.example.blogdemo.service;

import com.example.blogdemo.entity.Archive;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @Author Anita
 * @Date 2017/12/21 17:33
 */
@Service
public class ArchiveService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<Archive> selectArticleGroupByTime(Integer userId){

        String querySql = String.format("select article.id as articleId, article.title as articleTitle, YEAR(article.created_date) as year, MONTH(article.created_date) as month "+
                "from article where article.user_Id = %d group by YEAR(article.created_date),MONTH(article.created_date),article.id,article.title order by articleId desc",userId);
        RowMapper<Archive> rowMapper = new BeanPropertyRowMapper<>(Archive.class);
        return jdbcTemplate.query(querySql,rowMapper);
    }
}
